package dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import dao.BorrowDao;
import model.Borrow;
import util.JDBCUtiles;

public class BorrowDaoImpl implements BorrowDao{

	@Override
	public List<Borrow> getAllBorrow() throws SQLException {
		// 得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		// sql语句
		String sql = "select users.UserID,books.BookName,books.BookIsbn,borrow.BorrowTime,borrow.BorrowReturn,borrow.BorrowState from borrow,books,users WHERE users.UserID = borrow.Userid AND books.BookIsbn = borrow.BookIsbn";
		return qr.query(sql, new BeanListHandler<Borrow>(Borrow.class));
	}

	@Override
	public boolean borrowBook(Borrow borrow) throws SQLException {
		// 得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		// sql语句
		String sql = "insert IGNORE into borrow (Userid,BookIsbn,BorrowTime,BorrowDuration,`BorrowReturn`,BorrowState) "
				+ "values (?,?,CURDATE(),";
		if(borrow.getBorrowDuration() == 7) {
			sql += "'0000-00-07',(date_add(CURDATE(), interval 7 day)),0)";
		}else if(borrow.getBorrowDuration() == 30){
			sql += "'0000-00-30',(date_add(CURDATE(), interval 30 day)),0)";
		}
		int i = 0;
		i = qr.update(sql, borrow.getUserId(),borrow.getBookIsbn());
		String sql2 = "update books set BookNum=BookNum-? where BookIsbn = ?";
		qr.update(sql2,i,borrow.getBookIsbn());
		return i > 0 ? true : false;
	}

	@Override
	public boolean adminAgreeBorrow(Borrow borrow) throws SQLException {
		// 得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		// sql语句
		String sql = "UPDATE borrow SET BorrowState = 1 WHERE userid = ? and BookIsbn = ?";
		int i = qr.update(sql, borrow.getUserId(),borrow.getBookIsbn());
		return i > 0 ? true : false;
	}

	@Override
	public List<Borrow> getBorrowByUserId(String userid) throws SQLException {
		// 得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		// sql语句
		String sql = "select books.BookName,books.BookIsbn,borrow.BorrowTime,borrow.BorrowReturn,borrow.BorrowState from borrow,books,users WHERE users.UserID = borrow.Userid AND books.BookIsbn = borrow.BookIsbn and users.userid = ?";
		return qr.query(sql,new BeanListHandler<Borrow>(Borrow.class),userid);
	}

	@Override
	public boolean returnBook(Borrow borrow) throws SQLException {
		// 得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		// sql语句
		String sql = "delete from borrow where userid = ? and BookIsbn = ?";
		int i = qr.update(sql, borrow.getUserId(),borrow.getBookIsbn());
		String sql2 = "update books set BookNum=BookNum+? where BookIsbn = ?";
		qr.update(sql2,i,borrow.getBookIsbn());
		return i > 0 ? true : false;
	}

}
